
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


DROP TABLE IF EXISTS `eai_appraisal`;
CREATE TABLE IF NOT EXISTS `eai_appraisal` (
  `id_eai` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL,
  `id_manager` int(11) NOT NULL,
  `id_manager_interview` int(11) DEFAULT NULL,
  `date` date NOT NULL,
  `date_valid_manager` date DEFAULT NULL,
  `date_valid_user` date DEFAULT NULL,
  `id_template` tinyint(4) NOT NULL,
  `id_status` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_eai`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

DROP TABLE IF EXISTS `eai_appraisal_data`;
CREATE TABLE IF NOT EXISTS `eai_appraisal_data` (
  `id_eai` int(11) NOT NULL,
  `id_criterion` int(11) NOT NULL,
  `label` varchar(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  `comment` text,
  UNIQUE KEY `id_eai` (`id_eai`,`id_criterion`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='table dans laquelle les données des évaluations sont stockées';


DROP TABLE IF EXISTS `eai_campaign`;
CREATE TABLE IF NOT EXISTS `eai_campaign` (
  `id_campaign` int(11) NOT NULL AUTO_INCREMENT,
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  PRIMARY KEY (`id_campaign`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;


DROP TABLE IF EXISTS `eai_criterion`;
CREATE TABLE IF NOT EXISTS `eai_criterion` (
  `id_criterion` int(11) NOT NULL AUTO_INCREMENT,
  `id_theme` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  `label` varchar(255) DEFAULT NULL,
  `id_grade` int(11) DEFAULT NULL,
  `default_value` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `activate` tinyint(4) NOT NULL,
  `compulsory` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_criterion`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;


DROP TABLE IF EXISTS `eai_grade`;
CREATE TABLE IF NOT EXISTS `eai_grade` (
  `id_grade` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL,
  `min_value` int(11) DEFAULT NULL,
  `max_value` int(11) DEFAULT NULL,
  `scale` int(11) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `id_list` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_grade`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;


INSERT INTO `eai_grade` (`id_grade`, `type`, `min_value`, `max_value`, `scale`, `size`, `id_list`) VALUES
(1, 1, 0, 10, 1, NULL, NULL),
(2, 2, NULL, NULL, NULL, NULL, 2),
(3, 3, NULL, NULL, NULL, NULL, NULL),
(4, 2, NULL, NULL, NULL, NULL, 3),
(5, 2, NULL, NULL, NULL, NULL, 4),
(6, 2, NULL, NULL, NULL, NULL, 5),
(7, 2, NULL, NULL, NULL, NULL, 6),
(8, 2, NULL, NULL, NULL, NULL, 1),
(9, 2, NULL, NULL, NULL, NULL, 7),
(10, 2, NULL, NULL, NULL, NULL, 8);


DROP TABLE IF EXISTS `eai_list`;
CREATE TABLE IF NOT EXISTS `eai_list` (
  `id_list` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  `label` varchar(255) NOT NULL,
  `value` int(11) NOT NULL,
  UNIQUE KEY `id_list_2` (`id_list`,`rank`),
  KEY `id_list` (`id_list`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `eai_list` (`id_list`, `rank`, `label`, `value`) VALUES
(1, 1, 'Inadaptée aux besoins', 0),
(1, 2, 'Insatisfaisant', 1),
(1, 3, 'Acceptable', 2),
(1, 4, 'Très satisfaisant', 3),
(1, 5, 'Excellent', 4),
(2, 1, 'Non applicable', 0),
(2, 2, 'Insuffisant', 1),
(2, 3, 'A améliorer', 2),
(2, 4, 'Bien', 3),
(2, 5, 'Très Bien', 4),
(2, 6, 'Excellent', 5),
(3, 1, 'Reporté', 0),
(3, 2, 'Réalisé à moins de 50%', 1),
(3, 3, 'Réalisé à plus de 50%', 2),
(3, 4, 'Atteint', 3),
(3, 5, 'Dépassé', 4),
(4, 1, 'En déclin', 0),
(4, 2, 'Stable', 1),
(4, 3, 'Amélioration', 2),
(5, 1, 'Pas du tout', 0),
(5, 2, 'Plutôt non', 1),
(5, 3, 'Normal', 2),
(5, 4, 'Plutôt oui', 3),
(5, 5, 'Tout à fait', 4),
(6, 1, 'Non', 0),
(6, 2, 'Oui', 1),
(7, 1, 'Souhaitée', 0),
(7, 2, 'Essentielle', 1),
(7, 3, 'Prioritaire', 2),
(8, 1, 'Insuffisant', 0),
(8, 2, 'A améliorer', 1),
(8, 3, 'Bien', 2),
(8, 4, 'Très Bien', 3),
(8, 5, 'Excellent', 4);


DROP TABLE IF EXISTS `eai_stage`;
CREATE TABLE IF NOT EXISTS `eai_stage` (
  `id_stage` int(11) NOT NULL AUTO_INCREMENT,
  `rank` tinyint(4) NOT NULL,
  `name` varchar(255) NOT NULL,
  `id_template` tinyint(4) NOT NULL,
  `activate` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_stage`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;


DROP TABLE IF EXISTS `eai_template`;
CREATE TABLE IF NOT EXISTS `eai_template` (
  `id_template` tinyint(4) NOT NULL,
  `designation` varchar(255) NOT NULL COMMENT 'nom personnalisable du modèle',
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id_template`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `eai_theme`;
CREATE TABLE IF NOT EXISTS `eai_theme` (
  `id_theme` int(11) NOT NULL AUTO_INCREMENT,
  `rank` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `id_stage` int(11) NOT NULL,
  `activate` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_theme`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

